package cn.ty.znsz.area;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.Ret;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.redis.Redis;

import cn.ty.znsz.common.model.SysArea;

public class AreaService {
	
	private SysArea areaDao=new SysArea().dao();
	
	/**
	 * 查询用户所属区划
	 * @param cookie
	 * @return
	 */
	public Ret getUserArea(String cookie) {
		String key=Redis.use().get(cookie);
		int areaId=Redis.use().hget(key, "areaId");
		Record record=Db.findFirst("select getParentListArea (?) id;",areaId);
		String[] areas=record.getStr("id").split(",");
		List<SysArea> list=new ArrayList<SysArea>();
		for(int i=areas.length-1;i>=0;i--) {
			SysArea temp=areaDao.findFirst("SELECT a.id,a.areaName from sys_area a WHERE a.id=?;",areas[i]);
			if(temp != null) {
				list.add(temp);
			}
		}
		return Ret.ok("操作成功").set("data", list);
	}
	/**
	 * 得到所有地市
	 * @return
	 */
	public Ret getAllCitys() {
		return Ret.ok("操作成功").set("data", areaDao.find("SELECT a.id,a.areaName from sys_area a where a.parentid=1;"));
	}
	/**
	 * 得到下一级区划
	 * @param paraToInt
	 * @return
	 */
	public Ret searchNextArea(Integer areaId) {
		return Ret.ok("操作成功").set("data", areaDao.find("SELECT d.id,d.areaName from sys_area d where d.parentId=?",areaId));
	}
	/**
	 * 得到该用户下的所有区划
	 * @param cookie
	 * @return
	 */
	public Ret getAreasById(String cookie) {
		String key=Redis.use().get(cookie);
		int areaId=Redis.use().hget(key, "areaId");
		SysArea sysArea=areaDao.findById(areaId);		
		return Ret.ok("操作成功").set("data", areaDao.find("SELECT a.id,a.parentId pId,a.areaName name from sys_area a WHERE a.areaCode LIKE '"+sysArea.getAreaCode()+"%';"));
	}
	/**
	 * 首页分页
	 * @param paraToInt
	 * @param paraToInt2
	 * @param para
	 * @param para2
	 * @param para3
	 * @param para4
	 * @param para5
	 * @param para6
	 * @return
	 */
	public Page<SysArea> paginate(Integer pageNumber, Integer pageSize, String city, String county, String town,
			String village, String areaName, String fullName) {
		String select="select * ";
		StringBuilder sql = new StringBuilder();
		sql.append("from sys_area a where 1=1");
		if(city != null && county == null){
			Record record=Db.findFirst("SELECT a.areaCode from sys_area a WHERE a.id=?;",city);
			sql.append(" and a.areaCode like '"+record.getStr("areaCode")+"%'");
		}
		if(county != null && town == null){
			Record record=Db.findFirst("SELECT a.areaCode from sys_area a WHERE a.id=?;",county);
			sql.append(" and a.areaCode like '"+record.getStr("areaCode")+"%'");
		}
		if(town != null && village == null){
			Record record=Db.findFirst("SELECT a.areaCode from sys_area a WHERE a.id=?;",town);
			sql.append(" and a.areaCode like '"+record.getStr("areaCode")+"%'");
		}
		if(village != null){
			Record record=Db.findFirst("SELECT a.areaCode from sys_area a WHERE a.id=?;",village);
			sql.append(" and a.areaCode like '"+record.getStr("areaCode")+"%'");
		}
		if(!"".equals(areaName) && areaName !=null){
			sql.append(" and a.areaName like '%"+areaName+"%'");
		}
		if(!"".equals(fullName) && fullName !=null){
			sql.append(" and a.fullName like '%"+fullName+"%'");
		}
		return areaDao.paginate(pageNumber, pageSize, select, sql.toString());
	}
	/**
	 * 区划保存
	 * @param data
	 * @param paraToInt
	 * @return
	 */
	public Ret areaSave(SysArea data, Integer parentId) {
		SysArea sa=areaDao.findFirst("SELECT a.fullName from sys_area a WHERE  a.id=?;",parentId);
		String fullName=sa.getFullName()+"."+data.getAreaName();
		data.setParentId(parentId);
		data.setFullName(fullName);
		//得到areaCode
		SysArea sa2=areaDao.findFirst("SELECT a.areaCode from sys_area a WHERE a.parentId=? ORDER BY length(a.areaCode) DESC,a.areaCode DESC LIMIT 1;",parentId);
		String aa;
		if(sa2 == null) {
			SysArea sa3=areaDao.findFirst("SELECT a.areaCode from sys_area a WHERE a.id=?;",parentId);
			aa=sa3.getAreaCode()+"00.";
		}else {
			aa=sa2.getAreaCode();
		}
    	String[] bb=aa.split("\\.");
    	String cc = "";
    	for(int i=0;i<bb.length-1;i++){
    		cc+=bb[i]+".";
    	}
    	String areaCode="";
    	String dd=bb[bb.length-1];
    	if(dd.startsWith("0")) {
    		if(dd.equals("09")) {
    			areaCode=cc+"10.";
	    	}else {
	    		areaCode=cc+"0"+(Integer.parseInt(dd)+1)+".";
	    	}
    	}else {
    		areaCode=cc+(Integer.parseInt(dd)+1)+".";
    	}
    	data.setAreaCode(areaCode);							
		data.use("zsql").save();	
		return Ret.ok("操作成功");
	}
	/**
	 * 区划修改
	 * @param data
	 * @param paraToInt
	 * @param paraToInt2
	 * @return
	 */
	public Ret areaUpdate(SysArea data, Integer id, Integer parentId) {
		SysArea sa=areaDao.findFirst("SELECT a.fullName from sys_area a WHERE  a.id=?;",parentId);
		SysArea sa2=areaDao.findFirst("SELECT a.parentId,a.fullName from sys_area a WHERE  a.id=?;",id);
		String fullName=sa.getFullName()+"."+data.getAreaName();//修改后fullname
		data.setId(id);
	    data.setParentId(parentId);
		//得到areaCode
		if(sa2.getParentId() != parentId) {//上级所属变动
			SysArea sa3=areaDao.findFirst("SELECT a.areaCode from sys_area a WHERE a.parentId=? ORDER BY length(a.areaCode) DESC,a.areaCode DESC LIMIT 1;",parentId);
			String aa=sa3.getAreaCode();
	    	String[] bb=aa.split("\\.");
	    	String cc = "";
	    	for(int i=0;i<bb.length-1;i++){
	    		cc+=bb[i]+".";
	    	}
	    	String areaCode="";
	    	String dd=bb[bb.length-1];
	    	if(dd.startsWith("0")) {
	    		if(dd.equals("09")) {
	    			areaCode=cc+"10.";
		    	}else {
		    		areaCode=cc+"0"+(Integer.parseInt(dd)+1)+".";
		    	}
	    	}else {
	    		areaCode=cc+(Integer.parseInt(dd)+1)+".";
	    	}
	    	data.setAreaCode(areaCode);	
		}else {
			String aaa=Db.queryStr("SELECT a.areaCode from sys_area a WHERE a.id=?;",parentId);
			String bbb=Db.queryStr("SELECT a.areaCode from sys_area a WHERE a.parentId=? ORDER BY length(a.areaCode) DESC,a.areaCode DESC LIMIT 1;",parentId);
			if(bbb.startsWith(aaa) ==false) {
				String aa;
				int t=Db.queryInt("SELECT COUNT(*) from sys_area a WHERE a.areaCode LIKE '"+aaa+"%';");
				if(t == 1) {
					aa=aaa+"00.";
				}else {
					SysArea rec3=areaDao.findFirst("SELECT a.areaCode from sys_area a WHERE a.areaCode LIKE '"+aaa+"%' ORDER BY length(a.areaCode) DESC,a.areaCode DESC LIMIT 1;");
					aa=rec3.getAreaCode();
				}
				String[] bb=aa.split("\\.");
		    	String cc = "";
		    	for(int i=0;i<bb.length-1;i++){
		    		cc+=bb[i]+".";
		    	}
		    	String areaCode="";
		    	String dd=bb[bb.length-1];
		    	if(dd.startsWith("0")) {
		    		if(dd.equals("09")) {
		    			areaCode=cc+"10.";
			    	}else {
			    		areaCode=cc+"0"+(Integer.parseInt(dd)+1)+".";
			    	}
		    	}else {
		    		areaCode=cc+(Integer.parseInt(dd)+1)+".";
		    	}
		    	data.setAreaCode(areaCode);	
			}	
		}	    
		data.use("zsql").update();
		Db.use("zsql").update("UPDATE sys_area set fullName=REPLACE(fullName,'"+sa2.getFullName()+"','"+fullName+"');");
		return Ret.ok("操作成功");
	}
	/**
	 * 区划删除
	 * @param paraToInt
	 * @return
	 */
	public Ret areaDel(Integer id) {
		new SysArea().use("zsql").deleteById(id);
		return Ret.ok("操作成功");
	}
	/**
	 * 根据code查询id
	 */
	public int getAreaIdByAreaCode(String areaCode) {
		SysArea sysArea=areaDao.findFirst("SELECT * from sys_area a WHERE a.areaCode=?;",areaCode);
		return sysArea.getId();
	}
}
